package sk.stuba.fiit.foo07.genex.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import sk.stuba.fiit.foo07.genex.beans.Keyword;

public class KeywordDaoDerby extends BasicDao implements KeywordDao {

    private ResultSet rs;
    private Keyword keyword;
    private ArrayList<Keyword> keywordList;
    private PreparedStatement pst;

    private static final String sqlAdd = "INSERT INTO KEYWORD VALUES( ? , DEFAULT )";
    private static final String sqlAddKeywordQuestion = "INSERT INTO KEYWORDQUESTION VALUES ( ? , ? )";
    private static final String sqlDelete = "DELETE FROM KEYWORD WHERE KEYWORDID = ?";
    private static final String sqlDeleteKeywordQuestion = "DELETE FROM KEYWORDQUESTION WHERE KEYWORDID = ?";
    private static final String sqlGetById = "SELECT * FROM KEYWORD WHERE KEYWORDID = ?";
    private static final String sqlGetIdsByQuestionId = "SELECT KEYWORDID FROM KEYWORDQUESTION WHERE QUESTIONID = ?";
    private static final String sqlUpdate = "UPDATE KEYWORD SET TEXT = ? WHERE KEYWORDID = ?";
    private static final String sqlGetAllKeywords = "SELECT * FROM KEYWORD";
    private static final String sqlDeleteKeywordsFromQuestion = "DELETE FROM KEYWORDQUESTION WHERE QUESTIONID = ?";

    public KeywordDaoDerby() throws Exception {
        super();
    }

    public KeywordDaoDerby(Connection con) throws SQLException {
        super(con);
    }

    @Override
    public void addKeyword(Integer questionID, Keyword toAdd)
            throws SQLException {
        try {

            //if the keyword does not exist
            //add it to KEYWORD table
            if (toAdd.getKeywordID() == null) {
                pst = con.prepareStatement(sqlAdd,
                        Statement.RETURN_GENERATED_KEYS);
                pst.setString(1, toAdd.getText());
                pst.execute();

                rs = pst.getGeneratedKeys();
                rs.next();

                int keywordId = rs.getInt(1);
                toAdd.setKeywordID(keywordId);
                rs.close();
            }
            pst = con.prepareStatement(sqlAddKeywordQuestion,
                    Statement.RETURN_GENERATED_KEYS);
            pst.setInt(1, toAdd.getKeywordID());
            pst.setInt(2, questionID);
            pst.execute();

        } catch (SQLException sqle) {
            pst = null;
            rs = null;
            throw sqle;
        }
    }

    @Override
    public void addKeywords(Integer questionID, ArrayList<Keyword> toAdd)
            throws SQLException {

        for (Keyword k : toAdd) {
            this.addKeyword(questionID, k);
        }

    }

    @Override
    public void deleteKeyword(Keyword toDelete) throws SQLException {
        try {
            pst = con.prepareStatement(sqlDeleteKeywordQuestion);
            pst.setInt(1, toDelete.getKeywordID());
            pst.execute();

            pst = con.prepareStatement(sqlDelete);
            pst.setInt(1, toDelete.getKeywordID());
            pst.execute();

        } catch (SQLException sqle) {
            pst = null;
            throw sqle;
        }
    }

    @Override
    public void deleteAllKeywordsFromQuestion(Integer questionID)
            throws SQLException {
        try {
            pst = con.prepareStatement(sqlDeleteKeywordsFromQuestion);
            pst.setInt(1, questionID);

            pst.execute();

        } catch (SQLException sqle) {
            pst = null;
            throw sqle;
        }
    }

    @Override
    public void deleteKeywords(ArrayList<Keyword> toDelete) throws SQLException {
        for (Keyword k : toDelete) {
            this.deleteKeyword(k);
        }
    }

    @Override
    public Keyword getKeywordByID(Integer keywordID) throws SQLException {
        try {
            pst = con.prepareStatement(sqlGetById);
            pst.setInt(1, keywordID);
            rs = pst.executeQuery();
            rs.next();

            keyword = new Keyword();
            keyword.setText(rs.getString("TEXT"));
            keyword.setKeywordID(rs.getInt("KEYWORDID"));

            rs.close();

            return keyword;

        } catch (SQLException sqle) {
            rs = null;
            pst = null;
            keyword = null;
            throw sqle;
        }
    }

    @Override
    public ArrayList<Keyword> getAllKeywords() throws SQLException {

        try {
            pst = con.prepareStatement(sqlGetAllKeywords,
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);

            rs = pst.executeQuery();
            rs.last();
            keywordList = new ArrayList<Keyword>(rs.getRow());
            rs.beforeFirst();
            while (rs.next()) {
                keyword = new Keyword();
                keyword.setText(rs.getString("TEXT"));
                keyword.setKeywordID(rs.getInt("KEYWORDID"));
                keywordList.add(keyword);
            }
            rs.close();

            return keywordList;

        } catch (SQLException sqle) {
            rs = null;
            pst = null;
            keyword = null;
            throw sqle;
        }
    }

    @Override
    public ArrayList<Keyword> getKeywordsByQuestionID(Integer questionID)
            throws SQLException {
        try {
            pst = con.prepareStatement(sqlGetIdsByQuestionId,
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            pst.setInt(1, questionID);
            rs = pst.executeQuery();
            rs.last();
            ArrayList<Integer> keywordIds = new ArrayList<Integer>(rs.getRow());
            rs.beforeFirst();

            while (rs.next()) {
                keywordIds.add(rs.getInt("KEYWORDID"));
            }
            rs.close();

            keywordList = new ArrayList<Keyword>();
            for (Integer id : keywordIds) {
                keywordList.add(this.getKeywordByID(id));
            }

            return keywordList;

        } catch (SQLException sqle) {
            pst = null;
            rs = null;
            keyword = null;
            keywordList = null;
            throw sqle;
        }
    }

    @Override
    public void updateKeyword(Keyword toUpdate) throws SQLException {
        try {
            pst = con.prepareStatement(sqlUpdate);
            pst.setString(1, toUpdate.getText());
            pst.setInt(2, toUpdate.getKeywordID());
            pst.execute();

        } catch (SQLException sqle) {
            pst = null;
            throw sqle;
        }
    }

}
